#!/usr/bin/python

import smtplib
#from email.mime.multipart import MIMEMultipart
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText

import util


def main():

    # Me conecto a la DB
    db, cursor = util.conectar()

    # Offset para inicio de llamada en formato 'HH:MM:SS'
    offset = '01:00:00'
    
    # Obtengo id, full_name, user_name y start_time de las llamadas asociadas
    # a Oportunidades que comienzen dentro de 'offset' tiempo

    sql = '''
    SELECT         
        calls.id,
        CONCAT( IFNULL(users.last_name, ' '), ' ', IFNULL(users.first_name, '')) AS full_name,
        users.user_name,
        CONVERT_TZ(calls.date_start, '+00:00', '-03:00') AS start_time,
        email_addresses.email_address     
    FROM
        calls          
        INNER JOIN          
        users ON calls.assigned_user_id =users.id, 
        email_addresses, email_addr_bean_rel  
    WHERE          
        calls.deleted =0 AND users.deleted =0          
        AND  CONVERT_TZ(calls.date_start, '+00:00', '-03:00')              
            BETWEEN NOW() AND  ADDTIME( NOW(), '%s')
        AND calls.parent_type='Opportunities'
        AND NOT calls.status IN ('Held', 'Not Held')    
        AND calls.parent_id IS NOT NULL 
        AND email_addr_bean_rel.email_address_id=email_addresses.id 
        AND email_addresses.deleted=0 
        AND email_addr_bean_rel.deleted=0 
        AND email_addr_bean_rel.bean_id=users.id'''%(offset)
        
    try:
        cursor.execute(sql)
    except:
        print (sql+'\n')
        
    user = 'sugarcrm'
    password = 'aLech9hi'
    #sender = '%s@maipuautomotores.com.ar'%(user)
    sender = 'NoResponder@Automatico.com'
    
    # Obtengo el resultado de la consulta
    datos = cursor.fetchall()
    
    for dato in datos:
        
        # Obtengo los datos del registro
        call_id=dato[0]
        user_full_name=dato[1]
        user_name=dato[2]
        call_start=dato[3]
        user_email=dato[4]
        
        subject = 'Llamada pendiente'
        receivers = user_email
        # Creo el mensaje que enviare por mail        
        message = create_message(subject=subject, sender=sender, to=receivers, id=call_id, full_name=user_full_name, starting=call_start)
        send_mail('zimbra.amaipu.com.ar', user=user, password=password, sender=sender, to=receivers, text=message)
        
        
        print 'hi there!'
    # Obtengo los usuario con llamadas que vencen en 'reminder_time' minutos

def create_message(subject=' ', sender='nobody', to='nobody', id='S/D', full_name='S/D', starting='S/D'):
    
    link = "http://sugarcrm.amaipu.com.ar/index.php?module=Calls&action=DetailView&record=%s"%(id)
    msg = MIMEMultipart('alternative')
    msg['Subject'] = subject
    msg['From'] = sender
    msg['To'] = to


    html = """\
        <html>
        <head></head>
        <body>
            <p>Estimado <b>%s</b>,<br><br>
            se le informa que UD. tiene una llama asociada a una <i>Oportunidad</i> en <b>SugarCRM</b>, 
            la cual tiene como fecha de comienzo <b>%s</b>.<br>
            
            Para visualizarla por favor haga clik <a href="%s">aqui</a>.<br>
            O bien ingrese a <a href="sugarcrm.amaipu.com.ar">SugarCRM</a>.<br><br>
            
            
            Saludos,<br> 
            
            Equipo SugarCRM.<br><br>
            <b>Importante</b>: NO RESPONDA ESTE EMAIL! Este es un mail generado automaticamente!
            
            </p>
        </body>
        </html>
        """%(full_name, starting, link)
    
    part1 = MIMEText(html, 'html')
    # Attach parts into message container.
    # According to RFC 2046, the last part of a multipart message, in this case
    # the HTML message, is best and preferred.
    msg.attach(part1)
    
    return msg.as_string()
    
def send_mail(serverURL=None, user=None, password=None, sender='', to='', text=''):
    """
    Usage:
    mail('somemailserver.com', 'me@example.com', 'someone@example.com', 'test', 'This is a test')
    """
    
    mailServer = smtplib.SMTP(serverURL)
    mailServer.login(user, password)
    mailServer.sendmail(sender, to, text)
    mailServer.quit()


if __name__ == '__main__':
    main()
    #user = 'sugarcrm'
    #password = 'aLech9hi'
    #sender = '%s@maipuautomotores.com.ar'%(user)
    #receivers = 'castro.ramiro@gmail.com'

    #message = """This is a test e-mail message."""
    #send_mail('zimbra.amaipu.com.ar', user=user, password=password,sender=sender, to=receivers, subject='test', text=message)
